﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccessLibrary;

namespace BusinessLayer
{
    public class ClsHolidays
    {
        public int HolidaysID { get; set; }
        public int HolidaysMasterID { get; set; }
        public DateTime? FromDate { get; set; }
        public DateTime? ToDate { get; set; }

        public DataTable GetAllHolidays()
        {
            using (OleDataClient data = new OleDataClient())
            {
                return data.GetDataTable("SELECT * FROM  HolidaysMaster , Holidays where  Holidays.HolidaysMasterID=HolidaysMaster.HolidaysMasterID", "Holidays");
            }
        }

        public void GetHoliday(int holidaysID)
        {
            using (OleDataClient data = new OleDataClient())
            {
                DataTable dt = data.GetDataTable("Select * from Holidays where HolidaysID=" + holidaysID.ToString(), "Holidays");
                if (dt.Rows.Count > 0)
                {
                    HolidaysID = Convert.ToInt32(dt.Rows[0]["HolidaysID"]);
                    HolidaysMasterID = Convert.ToInt32(dt.Rows[0]["HolidaysMasterID"]);
                    FromDate = Convert.ToDateTime(dt.Rows[0]["FromDate"]);
                    ToDate = Convert.ToDateTime(dt.Rows[0]["ToDate"]);
                }
            }
        }
        public bool IsHoliday(DateTime date)
        {
            bool returnValue = false;
            using (OleDataClient data = new OleDataClient())
            {
                string count = data.GetValue("select count(*) from Holidays where Format([FromDate],'yyyy-mm-dd') <= '" + Utility.GetDateYYYYMMDD(date) + "' and Format([ToDate],'yyyy-mm-dd') >= '" + Utility.GetDateYYYYMMDD(date) + "'");
                if (Convert.ToInt32(count) > 0)
                {
                    returnValue = true;
                }
                else
                {
                    returnValue = false;
                }
            }
            return returnValue;
        }
        public bool SaveHoliday(string KeyForSave)
        {
            using (OleDataClient data = new OleDataClient())
            {
                if (KeyForSave == "add")
                {

                    string count = data.GetValue("select count(*) from Holidays where HolidaysMasterID=" + HolidaysMasterID.ToString() );
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Hiliday already exist");
                    }
                    string query = "select count(*)  from Holidays  "
                                  + "where(  (Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(FromDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(FromDate) + "' )"
                                  + "or"
                                  + "(Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(ToDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(ToDate) + "' )"
                                  + "or"
                                  + "(Format([FromDate],'yyyy-mm-dd')  <  '" + Utility.GetDateYYYYMMDD(FromDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(ToDate) + "' ))";
                     count = data.GetValue(query);
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Holiday already exist given date");
                    }
                    data.ExecuteNonQuery("Insert into Holidays (HolidaysMasterID,FromDate,ToDate) values(" + HolidaysMasterID.ToString() + ",'" + FromDate + "','" + ToDate + "')");
                }
                else
                {
                    string query = "select count(*)  from Holidays  "
                                   + "where(  (Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(FromDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(FromDate) + "' )"
                                   + "or"
                                   + "(Format([FromDate],'yyyy-mm-dd')  <=  '" + Utility.GetDateYYYYMMDD(ToDate) + "'  and  Format([ToDate],'yyyy-mm-dd')  >= '" + Utility.GetDateYYYYMMDD(ToDate) + "' ))"
                                   + "and HolidaysID NOT IN (" + HolidaysID.ToString() + ")";
                    string count = data.GetValue(query);
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Holiday already exist given date");
                    }

                    count = data.GetValue("select count(*) from Holidays where HolidaysMasterID=" + HolidaysMasterID.ToString() + " and HolidaysID NOT IN (" + HolidaysID.ToString() + ")");
                    if (Convert.ToInt32(count) > 0)
                    {
                        throw new Exception("Hiliday already exist");
                    }

                    data.ExecuteNonQuery("Update Holidays set HolidaysMasterID = " + HolidaysMasterID + " , FromDate='" + FromDate + "', ToDate='" + ToDate + "'  where HolidaysID=" + HolidaysID.ToString());
                }
            }
            return true;
        }

        public void DeleteHoliday()
        {
            using (OleDataClient data = new OleDataClient())
            {
                data.ExecuteNonQuery("Delete from  Holidays where HolidaysID=" + HolidaysID.ToString());
            }
        }

    }

}
